package com.xwd.dao.impl;

import com.xwd.dao.DBUtils;
import com.xwd.dao.StudentDao;
import com.xwd.domain.Student;
import com.xwd.util.StudentEnum;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import java.util.Date;
import java.util.List;

public class StudentDaoImpl extends DBUtils implements StudentDao {

    @Override
    public List<Student> getAllStudent(String stuName,String stuNo,int sex,int index,int pageSize) {
        List list = new ArrayList<Student>();
        StringBuffer sql=new StringBuffer(" select * from student where 1=1 and state=1 ");
        try {
            List param = new ArrayList();
            if(stuName!=null&&stuName.length()>0){
                sql.append(" and stuname like ? ");
                param.add("%"+stuName+"%");
            }
            if(stuNo!=null&&stuNo.length()>0){
                sql.append(" and stuno=? ");
                param.add(stuNo);
            }
            if(sex!=-1){
                sql.append(" and sex=? ");
                param.add(sex) ;
            }

            sql.append(" limit ?,?");
            param.add((index-1)*pageSize);
            param.add(pageSize);

            System.out.println(sql);

            resultSet = query(sql.toString(), param);
            while (resultSet.next()){
                Student stu = new Student();
                stu.setStuId(resultSet.getInt("stuid"));
                stu.setStuNo(resultSet.getString("stuno"));
                stu.setStuName(resultSet.getString("stuname"));
                stu.setSex(resultSet.getInt("sex"));
                stu.setPhone(resultSet.getString("phone"));
                stu.setProfession(resultSet.getString("profession"));
                stu.setRegDate(resultSet.getDate("regdate"));
                list.add(stu);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return list;
    }


    @Override
    public int total(String stuName, String stuNo, int sex) {
        int coutnt=0;
        StringBuffer sql=new StringBuffer(" select count(*) from student where 1=1 and state=1 ");
        try {
            List param = new ArrayList();
            if (stuName != null && stuName.length() > 0) {
                sql.append(" and stuname like ? ");
                param.add("%" + stuName + "%");
            }
            if (stuNo != null && stuNo.length() > 0) {
                sql.append(" and stuno=? ");
                param.add(stuNo);
            }
            if (sex != -1) {
                sql.append(" and sex=? ");
                param.add(sex);
            }
            resultSet = query(sql.toString(), param);
            while (resultSet.next()){
                coutnt = resultSet.getInt(1);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            closeAll();
        }
        return coutnt;
    }

    @Override
    public int insertStu(Student student) {
        int i=0;
        try {
            String sql = "insert into student values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            //添加参数
            List param = new ArrayList();
            param.add(student.getStuName());
            param.add(student.getStuNo());
            param.add(student.getSex());
            param.add(student.getPhone());
            param.add(student.getEmail());
            param.add(student.getRegistered());
            param.add(student.getAddress());
            param.add(student.getProfession());
            param.add(student.getIdNumber());
            param.add(student.getPolitics());
            param.add(new Date());
            param.add(StudentEnum.REDING.type);
            param.add(student.getIntroduction());
            param.add(student.getGid());
//            for (Object o : param) {
//                System.out.println(o);
//            }
            i = update(sql, param);
            System.out.println(i);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            closeAll();
        }
        return i;
    }

    @Override
    public Student findById(int stuid) {
        Student stu =null;
        try {
            String sql = "select * from student where stuid=?";
            List param = new ArrayList();
            param.add(stuid);
            resultSet = query(sql, param);
            while (resultSet.next()){
                stu =new Student();
                stu.setStuId(stuid);
                stu.setStuNo(resultSet.getString("stuno"));
                stu.setStuName(resultSet.getString("stuname"));
                stu.setSex(resultSet.getInt("sex"));
                stu.setPhone(resultSet.getString("phone"));
                stu.setEmail(resultSet.getString("email"));
                stu.setRegistered(resultSet.getString("registered"));
                stu.setAddress(resultSet.getString("address"));
                stu.setProfession(resultSet.getString("profession"));
                stu.setIdNumber(resultSet.getString("idnumber"));
                stu.setPolitics(resultSet.getString("politics"));
                stu.setRegDate(resultSet.getDate("regdate"));
                stu.setState(resultSet.getInt("state"));
                stu.setIntroduction(resultSet.getString("introduction"));
                stu.setGid(resultSet.getInt("gid"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll();
        }
        System.out.println(stu);
        return stu;
    }

    @Override
    public int updateStudent(Student student) {
        int i = 0;
        try {
            String sql = "update student set stuname=?,stuno=?,sex=?,phone=?,email=?,registered=?," +
                    "address=?,profession=?,idnumber=?,politics=?,regdate=?,state=?,introduction=?,gid=? where stuid=?";
            //添加参数
            List param = new ArrayList();
            param.add(student.getStuName());
            param.add(student.getStuNo());
            param.add(student.getSex());
            param.add(student.getPhone());
            param.add(student.getEmail());
            param.add(student.getRegistered());
            param.add(student.getAddress());
            param.add(student.getProfession());
            param.add(student.getIdNumber());
            param.add(student.getPolitics());
            param.add(new Date());
            param.add(student.getState());
            param.add(student.getIntroduction());
            param.add(student.getGid());
            param.add(student.getStuId());
            i = update(sql, param);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            closeAll();
        }
        return i;
    }

    @Override
    public int deleteStudent(int stuId) {
        int i=0;
        try {
            List param = new ArrayList();
            String sql = "update student set state=0 where stuid=?";
            param.add(stuId);
            i = update(sql, param);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            closeAll();
        }
        return i;
    }

    @Override
    public Boolean findStuNoExist(String stuNo) {
        try {
            List param = new ArrayList();
            String sql = "select * from student where stuno=?";
            param.add(stuNo);
            resultSet = query(sql, param);
            if(resultSet.next()){
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            closeAll();
        }
        return false;
    }
}
